package light.head.dao.sys;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import light.head.cache.Cache;
import light.head.config.ConfigParm;
import light.head.constant.Parm;
import light.head.constant.Sys;
import light.head.dto.NewMessage;
import light.head.enm.RESULT;
import light.head.framework.base.BaseDAO;
import light.head.framework.entity.PageBean;
import light.head.framework.utils.DateUtils;
import light.head.model.FileInfo;
import light.head.model.Message;
import light.head.model.MessageAnnex;
import light.head.model.MessageReceive;
import light.head.model.Organ;
import light.head.model.UserInfo;

import org.springframework.stereotype.Service;

@Service
public class MessageDAO extends BaseDAO<Message, Integer> {
	private final static String sql_findAnnexByMsgCode = "FROM MessageAnnex ma WHERE ma.mc = ?";
	private final static String sql_findNotice = "FROM Message WHERE ip = 1 AND ip IS NOT NULL AND deleted <> 1 AND deleted IS NOT NULL ORDER BY ct DESC";
	private final static String sql_loadNoticeTotal = "SELECT COUNT(*) FROM Message WHERE ip = 1 AND ip IS NOT NULL AND deleted <> 1 AND deleted IS NOT NULL";
	private final static String sql_countByUser = "SELECT COUNT(*) FROM MessageReceive mr WHERE mr.iv <> 0 AND mr.ruc = ?";
	
	/**
	 * 根据查询条件分页查询当前用户相关的消息
	 * @param	当前用户
	 * @param	分页对象
	 * @param	查询条件
	 */
	public RESULT findByMap(Integer uc,Map<String, String> map, PageBean pageBean) {
		StringBuffer hql = new StringBuffer("FROM Message m WHERE 1 = 1");
		
		List<Object> list = null;
		if (map != null) {
			list = new ArrayList<Object>();
			String type = map.get(Parm.TYPE);					// 1.发送、2.接收、3.删除
			String title = map.get(Parm.TITLE);
			String start = map.get(Parm.START);
			String end = map.get(Parm.END);
			String iv = map.get(Parm.IV);
			if (isNotNull(title)) {
				hql.append(" AND m.title like ?");
				list.add( title + "%");
			}
			if (isNotNull(start)) {
				hql.append(" AND m.ct >= ?");
				list.add(DateUtils.getDateByStr(start, "yyyy-MM-dd"));
			}
			if (isNotNull(end)) {
				hql.append(" AND m.ct <= ?");
				list.add(DateUtils.getDateByStr(end, "yyyy-MM-dd"));
			}
			if (isNotNull(type)) {
				if (type.equals(Sys.S_ONE)) {
					hql.append(" AND m.deleted = 0 AND m.code IN (SELECT mr.mc FROM MessageReceive mr WHERE mr.deleted = 0 AND mr.ruc = ?" + (isNotNull(iv) ? " AND mr.iv = 0" : "") + ")");
				} else if (type.equals(Sys.S_TWO)) {
					hql.append(" AND m.deleted = 0 AND m.cu = ?");

				} else if (type.equals(Sys.S_THREE)) {
					hql.append(" AND ((m.deleted = 1 AND m.cu = ?) OR (m.code IN (SELECT mr.mc FROM MessageReceive mr WHERE mr.ruc = ? AND mr.deleted = 1)))");
					list.add(uc);
				}
				list.add(uc);
			}

		}
		
		hql.append(" ORDER BY m.code  DESC");
		
		return findToPageBean(hql.toString(), pageBean, list);
	}
	
		
		public RESULT update(Message message) {
			if (null == message || null == message.getCode())
				return RESULT.PARAMETER_ERROR;
			
			Message old = get(message.getCode());
			if (null == old)
				return RESULT.NOT_FIND;
			if(null !=(message.getDeleted())) {
				old.setDeleted(message.getDeleted());
			}
			RESULT result = upd(old);
			return result;
		}

		

	/**
	 * 根据用户编号，获取指定邮件的打开状态
	 * @param	邮件编号数组
	 * @param	用户编号
	 */
	public List<Object[]> findOpenStatus(List<Integer> codes, Integer uc) {
		if (null == codes || null == uc || codes.size() == 0)
			return null;
		
		List<Object> parm = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("SELECT mr.mc, mr.iv FROM MessageReceive mr WHERE mr.ruc = ? AND mr.mc IN (");
		parm.add(uc);
		for (Integer code : codes) {
			parm.add(code);
			sql.append("?,");
		}
		sql.deleteCharAt(sql.length() - 1).append(")");
		List<Object[]> list = query(sql.toString(), null, null, parm.toArray());
		return list;
	}
	/**
	 * 根据条件分页查询接收人信息
	 * @param	查询条件
	 * @param	分页对象
	 */
	public RESULT findUser(List<Integer> ocs,Map<String, String> map, PageBean pageBean) {
		StringBuffer hql = new StringBuffer("FROM UserInfo u WHERE u.realName <> 'admin' AND u.deleted <> 1 AND u.deleted IS NOT NULL AND u.oc IN (");
		List<Object> list = new ArrayList<Object>();
		for (Integer i : ocs) {
			list.add(i);
			hql.append("?,");
		}
		hql.deleteCharAt(hql.length() - 1).append(")");
		if (map != null) {

			String realName = map.get(Parm.REAL_NAME);
			String oc = map.get(Parm.OC);
			String suc = map.get(Parm.SUC);

			if (isNotNull(realName)) {
				hql.append(" AND u.realName LIKE ?");
				list.add("%" + realName + "%");
			}
			if (isNotNull(oc)) {
				hql.append(" AND u.oc = ?");
				list.add(Integer.parseInt(oc));
			}
			if (isNotNull(suc)) {
				hql.append(" AND u.code NOT IN (" + suc + ")");
			}
		}
		hql.append(" ORDER BY u.oc, u.code  DESC");
		
		return findToPageBean(hql.toString(), pageBean, list);
	}
	
	
	/**
	 * 根据条件分页查询接收机构信息
	 * @param	当前用的机构编号
	 * @param	查询条件
	 * @param	分页对象
	 */
	public RESULT findOrgan(Integer oc, Map<String, String> map, PageBean pb) {
		StringBuffer hql = new StringBuffer("FROM Organ o WHERE o.deleted <> 1 AND o.deleted IS NOT NULL");

		List<Object> parm = null;
		if (null != map) {
			parm = new ArrayList<Object>();
			
			String name = map.get(Parm.NAME);
			String type = map.get(Parm.TYPE);					// 1.当前用户的所在机构的所有上下级机构，2.当前用户的供应商机构，3.当前用户的经销商机构
			String soc = map.get(Parm.SOC);
			
			if (isNotNull(name)) {
				hql.append(" AND o.name LIKE ?");
				parm.add("%" + name + "%");
			}
			if (isNotNull(type)) {
				if (type.equals(Sys.S_ONE)) {
					String codes = map.get(Parm.CODES);
					hql.append(" AND o.code IN (" + codes + ")");
				} else if (type.equals(Sys.S_TWO)) {
					// 当type=2时，查找当前用户所属机构的供应商
					parm.add(oc);
					hql.append(" AND o.code IN (SELECT orrn.supplier FROM OrganRelation orrn WHERE orrn.distributor = ?)");
				} else if (type.equals(Sys.S_THREE)) {
					// 当type=3时，查找当前用户所属机构的经销商
					parm.add(oc);
					hql.append(" AND o.code IN (SELECT orrn.distributor FROM OrganRelation orrn WHERE orrn.supplier = ?)");
				}
			}
			if (isNotNull(soc)) {
				hql.append(" AND o.code NOT IN (" + soc + ")");
			}
		}
		hql.append(" ORDER BY o.code  DESC");
		return findToPageBean(hql.toString(), pb, parm);
	}
	
	/**
	 * 根据拼接好的机构编号序列获取对应的机构列表
	 */
	public List<Organ> findOrganByCodes(String codes) {
		if (!isNotNull(codes)) {
			return null;
		}
		String sql = "FROM Organ o WHERE o.deleted <> 1 AND o.deleted IS NOT NULL AND o.code IN (" + codes + ")";
		List<Organ> list = find(sql);
		return list;
	}
	
	
	/**
	 * 通过邮件ID查找发件人
	 * @param ruc
	 * @param mc
	 * @return
	 */
	public UserInfo findUserByEmailCode(Integer ruc, Integer mc){
		if (null == ruc || ruc < 0 || null == mc || mc < 0) return null;
		
		StringBuffer sql = new StringBuffer("FROM UserInfo u where u.code = (SELECT mc FROM MessageReceive er WHERE er.ruc = ? AND mc = ?)");
		return (UserInfo) get(sql.toString(), mc, ruc);
	}
	
	
	/**
	 * 根据拼接好的用户编号序列获取对应的用户列表
	 */
	public List<UserInfo> findUserByCodes(String codes) {
		if (!isNotNull(codes)) {
			return null;
		}
		String sql = "FROM UserInfo u WHERE u.deleted <> 1 AND u.deleted IS NOT NULL AND u.code IN (" + codes + ")";
		List<UserInfo> list = find(sql);
		return list;
	}

	
	
	/**
	 * 根据拼接好的机构编号序列获取这些机构下的所有用户
	 */
	public List<UserInfo> findUserByOrganCodes(String codes) {
		if (!isNotNull(codes)) {
			return null;
		}
		String sql = "FROM UserInfo u WHERE u.deleted <> 1 AND u.deleted IS NOT NULL AND u.oc IN (" + codes + ")";
		List<UserInfo> list = find(sql);
		return list;
	}
	
	/**
	 * 根据消息编号获取该消息相关的附件
	 * @param	消息编号
	 */
	public List<MessageAnnex> findAnnexByMsgCode(Integer code) {
		List<MessageAnnex> list = find(sql_findAnnexByMsgCode, code);
		return list;
	}
	
	/**
	 * 获取指定用户的未读邮件总数
	 * @param	用户编号
	 */
	public Integer getTotalByUserCode(Integer code) {
		if (null == code)
			return null;
		
		String sql_findTotalByUserCode = "SELECT COUNT(*) FROM MessageReceive mr WHERE mr.iv = 0 AND mr.ruc = ?";
		List<Object> list = query(sql_findTotalByUserCode, null, null, code);
		if (null == list || list.size() == 0)
			return null;
		
		return ((BigInteger)list.get(0)).intValue();
	}
	
	/**
	 * 根据用户编号获取指定数量的消息
	 * @param	用户编号
	 * @param	要获取的消息记录数
	 * @return	返回值说明：0.消息编号	1.标题	2.发送人姓名	3.发送时间	4.是否查看
	 */
	public List<Object[]> findByUserCode(Integer code, int count) {
		//"FROM Message m WHERE m.code IN (SELECT mr.mc FROM MessageReceive mr WHERE mr.ruc = ?) ORDER BY m.ct DESC";
		String sql_findByUserCode = "SELECT m.code, m.title, u.realName, m.ct, mr.iv"
									+ " FROM Message m, MessageReceive mr, UserInfo u"
									+ " WHERE m.code = mr.mc AND m.cu = u.code"
									+ " AND m.ip <> 1 AND mr.ruc = ? AND mr.deleted <> 1 AND mr.deleted IS NOT NULL"
									+ " AND m.deleted <> 1 AND m.deleted IS NOT NULL";
		List<Object[]> list = query(sql_findByUserCode, 0, count, code);
		return list;
	}
	
	/**
	 * 通过邮件CODE查询邮件附件
	 * @param code
	 * @return
	 */
	public Object findAnnexByMessageCode (Integer code){
		if (null == code || code < 0) return null;
		
		StringBuffer sql = new StringBuffer("FROM EmailAnnex ea WHERE ea.ec = ?");
		return get(sql.toString(), code);
	}
	
	
	/**
	 * 根据消息编号获取该消息相关的附件
	 * @param	消息编号
	 */
	public List<MessageAnnex> findAnnexByMeesgCode(Integer code) {
		List<MessageAnnex> list = find(sql_findAnnexByMsgCode, code);
		return list;
	}
	/**
	 * 加载指定用户的所有邮箱信息到缓存，并以JSON方式保存
	 * @param	
	 */
	public List<NewMessage> findEmailList(Integer userCode) {
		
		StringBuffer sql = new StringBuffer("FROM Message m WHERE m.code IN ( SELECT  mr.mc FROM MessageReceive mr WHERE mr.iv = 0 AND mr.ruc = ? )  ORDER BY m.code DESC");
		List<Object> parm = new ArrayList<Object>();
		parm.add(userCode);
		List<Message> list = find(sql.toString(), parm.toArray());
		if (null == list || list.size() == 0)
			return null;
		
		NewMessage temp = null;
		List<NewMessage> result = new ArrayList<NewMessage>();
		for (Message o : list) {
			temp = new NewMessage();
			temp.setCode(o.getCode());
			temp.setTitle(o.getTitle());
			temp.setCt(DateUtils.getDateStr((Date)o.getCt(), DateUtils.MM_dd));
			temp.setRealName(o.getCun());
			
			result.add(temp);
		}
		return result;
	}
	/**
	 * 根据拼接好的文件编号序列获取对应的文件列表
	 */
	public List<FileInfo> findFileByCodes(String codes) {
		if (!isNotNull(codes)) {
			return null;
		}
		String sql = "FROM FileInfo f WHERE f.code IN (" + codes + ")";
		List<FileInfo> list = find(sql);
		return list;
	}
	
	public Message getNoticeByCode(Integer code) {
		if (null == code)
			return null;
		
		if (null == Cache.noticeList || Cache.noticeList.size() == 0)
			loadNotice();
		
		for (Message msg : Cache.noticeList) {
			if (msg.getCode().equals(code))
				return msg;
		}
		return null;
	}
	
	public List<Message> getNotice() {
		if (null == Cache.noticeList || Cache.noticeList.size() == 0)
			loadNotice();
		return Cache.noticeList;
	}
	
	public RESULT loadNotice() {
		List<Message> list = find(sql_findNotice, 0, ConfigParm.getCacheNoticeTotal());
		if (null != list && list.size() > 0) {
			StringBuffer sql = new StringBuffer("FROM MessageAnnex ma WHERE ma.mc IN (");
			List<Object> parm = new ArrayList<Object>();
			for (Message m : list) {
				sql.append("?,");
				parm.add(m.getCode());
			}
			sql.deleteCharAt(sql.length() - 1).append(")");
			
			List<MessageAnnex> maList = find(sql.toString(), null, null, parm.toArray());
			
			
			if (null != maList && maList.size() > 0) {
				for (Message m : list) {
					for (MessageAnnex ma : maList) {
						if (m.getCode().equals(ma.getMc())) {
							if (m.getAnList() == null)
								m.setAnList(new ArrayList<MessageAnnex>());
							
							m.getAnList().add(ma);
						}
					}
				}
			}
			Cache.noticeList = list;
			
			// 暂时在这里加载公告总数
			loadNoticeTotal();
			
			return RESULT.SUCCESS;
		}
		return RESULT.ERROR;
	}
	
	public RESULT findNotice(PageBean pb) {
		List<Message> list = find(sql_findNotice, pb.getStart(), pb.getPageSize());
		
		if (null != list && list.size() > 0) {
			StringBuffer sql = new StringBuffer("FROM MessageAnnex ma WHERE ma.mc IN (");
			List<Object> parm = new ArrayList<Object>();
			for (Message m : list) {
				sql.append("?,");
				parm.add(m.getCode());
			}
			sql.deleteCharAt(sql.length() - 1).append(")");
			
			List<MessageAnnex> maList = find(sql.toString(), null, null, parm.toArray());
			
			
			if (null != maList && maList.size() > 0) {
				for (Message m : list) {
					for (MessageAnnex ma : maList) {
						if (m.getCode().equals(ma.getMc())) {
							if (m.getAnList() == null)
								m.setAnList(new ArrayList<MessageAnnex>());
							
							m.getAnList().add(ma);
						}
					}
				}
			}
			pb.setResultList(list);
		}
		return RESULT.SUCCESS;
	}
	
	/** 
	 * 加载公告总数
	 */
	public BigInteger loadNoticeTotal() {
		List<Object> list = query(sql_loadNoticeTotal, null, null);
		if (null != list && list.size() > 0) {
			Cache.noticeTotal = (BigInteger)list.get(0);
		}
		return Cache.noticeTotal;
	}
	

	/**
	 * 获取指定用户的未读邮件数
	 * @param	用户编号
	 */
	public Integer countByUser(Integer code) {
		if (null == code)
			return null;
		
		List<BigInteger> list = query(sql_countByUser, null, null, code);
		if (null != list && list.size() > 0)
			return list.get(0).intValue();
		
		return null;
	}
	
	
	
	/**
	 * 发送消息给指定机构的所有人
	 * @param	标题
	 * @param	内容
	 * @param	发送人
	 * @param	发送人所属机构编号
	 * @param	接收机构编号
	 */
	public RESULT sendToAllUserByOrgan(String title, String content, Integer cu, Integer oc, Integer roc) {
		Message m = new Message();
		m.setCu(cu);
		m.setDeleted(false);
		m.setCt(new Date());
		m.setOc(oc);
		m.setTitle(title);
		m.setContent(content);
		m.setIp(false);
		
		if (add(m) == RESULT.SUCCESS) {
			String sql = "INSERT INTO MessageReceive(mc, ruc, iv, deleted) SELECT " + m.getCode() + ", u.code, 0, 0 FROM UserInfo u WHERE u.oc = ?";
			executeSql(sql, roc);
		}
		return RESULT.SUCCESS;
	}
	
	
	/**
	 * 发送消息给指定机构的所有管理员
	 * @param	标题
	 * @param	内容
	 * @param	发送人
	 * @param	发送人所属机构编号
	 * @param	接收机构编号
	 */
	public RESULT sendToAllManagerByOrgan(String title, String content, Integer cu, Integer oc, Integer roc) {
		Message m = new Message();
		m.setCu(cu);
		m.setDeleted(false);
		m.setCt(new Date());
		m.setOc(oc);
		m.setTitle(title);
		m.setContent(content);
		m.setIp(false);
		
		if (add(m) == RESULT.SUCCESS) {
			String sql = "INSERT INTO MessageReceive(mc, ruc, iv, deleted) SELECT " + m.getCode() + ", u.code, 0, 0 FROM UserInfo u WHERE u.isManager = 1 AND u.oc = ?";
			executeSql(sql, roc);
		}
		return RESULT.SUCCESS;
	}
	
	
	/**
	 * 发送消息给指定机构的所有人
	 * @param	标题
	 * @param	内容
	 * @param	发送人
	 * @param	发送人所属机构编号
	 * @param	接收机构编号
	 */
	public RESULT sendToUser(String title, String content, Integer cu, Integer oc, Integer ruc) {
		Message m = new Message();
		m.setCu(cu);
		m.setDeleted(false);
		m.setCt(new Date());
		m.setOc(oc);
		m.setTitle(title);
		m.setContent(content);
		m.setIp(false);
		
		if (add(m) == RESULT.SUCCESS) {
			MessageReceive mr = new MessageReceive();
			mr.setRuc(ruc);
			mr.setMc(m.getCode());
			mr.setIv(false);
			mr.setDeleted(false);
			
			return add(mr);
		}
		return RESULT.ERROR;
	}

	/**
	 * 发送消息给rucs指定的所有用户
	 * @param	标题
	 * @param	内容
	 * @param	发送人
	 * @param	发送人所属机构编号
	 * @param	接收人编号数组
	 */
	public RESULT sendToUsers(String title, String content, Integer cu, Integer oc, List<Integer> rucs) {
		if (null == rucs || rucs.size() == 0)
			return RESULT.PARAMETER_ERROR;
		
		Message m = new Message();
		m.setCu(cu);
		m.setDeleted(false);
		m.setCt(new Date());
		m.setOc(oc);
		m.setTitle(title);
		m.setContent(content);
		m.setIp(false);
		
		if (add(m) == RESULT.SUCCESS) {
			MessageReceive mr = null;
			for (Integer ruc : rucs) {
				mr = new MessageReceive();
				mr.setRuc(ruc);
				mr.setMc(m.getCode());
				mr.setIv(false);
				mr.setDeleted(false);
				add(mr);
			}
			return RESULT.SUCCESS;
		}
		return RESULT.ERROR;
	}
	/**
	 * 发送消息给rucs指定的所有用户
	 * @param	标题
	 * @param	内容
	 * @param	发送人
	 * @param	发送人所属机构编号
	 * @param	接收人编号数组
	 */
	public RESULT sendToUsers(String title, String content, Integer cu, Integer oc, Integer au) {
		if (null == cu )
			return RESULT.PARAMETER_ERROR;
		
		Message m = new Message();
		m.setCu(cu);
		m.setDeleted(false);
		m.setCt(new Date());
		m.setOc(oc);
		m.setTitle(title);
		m.setContent(content);
		m.setIp(false);
		
		if (add(m) == RESULT.SUCCESS) {
			MessageReceive mr = null;
				mr = new MessageReceive();
				mr.setRuc(au);
				mr.setMc(m.getCode());
				mr.setIv(false);
				mr.setDeleted(false);
				add(mr);
			return RESULT.SUCCESS;
		}
		return RESULT.ERROR;
	}
}
